/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package br.great.DAO;



import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import br.great.classes.TexteRange;
import br.great.beans.Address;
import br.great.beans.InvertedIndex;
import br.great.beans.Photo;
import br.great.beans.Point;
import br.great.beans.Tag;
//import org.steamer.photomap_desktop.typeTags.Type_Tags;
import br.great.functionalCore.TagIndexCreator;
//import org.steamer.photomap_desktop.functionalCore.ChangeFirstLetter;
/**
 *
 * @author windson
 */
public class DAO_Inverted_Extended_Index {


    private static volatile DAO_Inverted_Extended_Index instaceDAO_InvertedExtendedIndex = new DAO_Inverted_Extended_Index();

    /** The private constructor of DAO_InvertedIndex for singleton
     */

    

    /** The method for get the instance of the class DAO_InvertedIndex
     * @return DAO_InvertedIndex instaceDAO_InvertedIndex
     */
    public static DAO_Inverted_Extended_Index getInstance() {
        //if(instaceDAO_InvertedIndex==null)
        synchronized (DAO_InvertedIndex.class) {
            if (instaceDAO_InvertedExtendedIndex == null) {
                instaceDAO_InvertedExtendedIndex = new DAO_Inverted_Extended_Index();
            }
        }
        return instaceDAO_InvertedExtendedIndex;
    }
    private void insertInvertedExtededIndexHasPhoto(int iv_index_extended_id, int id_multimedia, double weight){
        try {
            PgConnect conn = PgConnect.getInstance();
            String insertIVIE_Photo = "insert into inverted_index_extended_has_photo values(" + iv_index_extended_id + "," + id_multimedia + "," + weight + ");";
            conn.executeInsert(insertIVIE_Photo);
        } catch (SQLException ex) {
            Logger.getLogger(DAO_Inverted_Extended_Index.class.getName()).log(Level.SEVERE, null, ex);
        }
    
    }

    private int insertInvertedExtendedIndex(InvertedIndex tag){
         PgConnect conn = PgConnect.getInstance();
         int i=0;
        if (tag != null) {
            String tagValue = TexteRange.tradeText(tag.getTAG_VALUE());
            if (!tagValue.contains("null")) {
                // put the first character of String to majuscule
//            ChangeFirstLetter cfl = new ChangeFirstLetter();
//            tagValue = cfl.changeFirstLetter(tagValue);

                // get tag for looking for the tag is existe or not
                String getTag = "select * from Inverted_Index_Extended where Lower(tag_value)=Lower('" + tagValue + "');";
                // String createSeqTag = "create sequence seqmax_tag increment by 1;";
                //  String addCounter = "create sequence seqmax_" + tagValue + " increment by 1;";
                //      String insertTag = "insert into InvertedIndex values(nextval('seqmax_tag'),'" + tagValue + "',nextval('seqmax_" + tagValue + "'), '" + tag.getInvertedIndex_TYPE() + "');";
//System.out.println(insertTag);
                // insert into the Tag a new value

                String getIdTag = "select last_value as max from seqmax_inverted_extended_index";

                try {
                    ResultSet rs_getTag = conn.executeQuery(getTag);
                    // if the tag is in the table
                    if (rs_getTag != null) {
                        if (rs_getTag.next()) {
                            // after do the insert, get the value of the ID Tag
                            i = rs_getTag.getInt("IV_INDEX_EXTENDED_ID");
                            String updateCounter = "update Inverted_Index_Extended set tag_counter=tag_counter+1 where IV_INDEX_EXTENDED_ID=" + i + ";";
//System.out.println(updateCounter);
                            conn.executeInsert(updateCounter);

                        } else { // if th tag isn't in table, we insert the tag in it

                            

                           // String addCounter = "create sequence seqmax_InvertedIndex" + (i) + " increment by 1;";
                            //conn.executeQuery(addCounter);
                            String insertTag = "insert into Inverted_Index_Extended values(nextval('seqmax_inverted_extended_index'),'" + tagValue + "',1,'" + tag.getTAG_TYPE() + "');";
                            conn.executeInsert(insertTag);
                           //Calculatig the max id of the table tag
                            try {
                                ResultSet rs = conn.executeQuery(getIdTag);
                                if(rs.next()) {
                                    i = rs.getInt("max");
                                }else{
                                    i=0;
                                }                               
                            } catch (Exception e) {
                                //First InvertedIndex
                                e.printStackTrace();
                                i = 0;
                            }                           
                           // System.err.println(insertTag);
                        }
                    }
                } catch (SQLException ex) {
                    System.err.println("error of insert Inverted_Index_Extended");
                    ex.getStackTrace();
                }
            }
        }
        conn.Close();
        // System.err.println("I:"+i);
        return i;
    
    }
    public void calculateAndInsertSpatialExpandedIndex(Address address, Photo photo, int id_multimedia) {
        //First Search the semantic similar and computes the semantic similarity
        String id_commune=address.getIdCityInDB();
        Point photoCoord=photo.getCoor();
        double alpha=1;
        double beta=2;
        double theta=0.65;
        double increaseDistance=100;
        double threshold=0.55;
        TagIndexCreator tagStamp=new TagIndexCreator();
        PgConnect conn = PgConnect.getInstance();
         // Get Name of similar cities
        String similarCities = "SELECT c2.nom_commune," +
                "("+theta+"* (" +
                    "2*(AVG(SSS.DEPTH_LSB))::NUMERIC" +
                    "/" +
                        "(AVG(DEPTH_LSB_COMMUNE)+AVG(DEPTH_LSB_COMMUNE_SIMILAR)+" +
                        "(1-SUM(ARE_NEIGHBOURS))*"+alpha+"+ (1-SUM(COM_SIM_IS_CAPITAL))*"+beta+")" +
                     ")" +
                 "+" +
                 "(1-"+theta+")*(" +
                 "1" +
                 "/(ST_DISTANCE(min(c2.contour),'POINT("+photoCoord.getLatitude()+" "+photoCoord.getLongitude()+")')*"+increaseDistance+"+1)))as SimSpatial" +
                 " FROM SEMANTIC_SPATIAL_SIMILARITY sss, commune c2" +
                 " where sss.id_commune='"+id_commune+"'" +
                 " and sss.id_commune_similar=c2.id_commune" +               
                 " GROUP by c2.nom_commune";
               

           
                    ResultSet rs_getSimCities = conn.executeQuery(similarCities);
                    // if the address is in the table
                    if (rs_getSimCities != null) {
                        try {
                            double SimSpatial;
                            String nom_commune;
                            InvertedIndex tag=new InvertedIndex();
                            tag.setTAG_TYPE("LOCATEDIN");
                            int id_tag;
                            while (rs_getSimCities.next()) {
                               SimSpatial= rs_getSimCities.getDouble("SimSpatial");
                               if(SimSpatial>=threshold){
                                nom_commune= rs_getSimCities.getString("nom_commune"); 
                               // System.out.println ("nom_commune:"+nom_commune + " SimSpatial:"+SimSpatial);
                                tag.setTAG_VALUE(tagStamp.getAddressTag(nom_commune));
                                //Insert Inverted Extended Index
                                id_tag=this.insertInvertedExtendedIndex(tag);
                                //insert into Inverted_Extende_Index Has Photo
                                this.insertInvertedExtededIndexHasPhoto(id_tag, id_multimedia, SimSpatial);
                                
                               }
                                
                            }
                        } catch (SQLException ex) {
                            Logger.getLogger(DAO_Inverted_Extended_Index.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }//end of if    


                        
    }


    public static void main(String[] args) {
        DAO_Inverted_Extended_Index dt = DAO_Inverted_Extended_Index.getInstance();
        Address address= new Address();
        address.setIdCityInDB("33305"); //Saint denis
        Photo p= new Photo();
        Point point= new Point(); //Stade de France
                point.setLatitude(48.924451991341975);
                point.setLongitude(2.36010789871);
        p.setCoor(point);
        dt.calculateAndInsertSpatialExpandedIndex(address, p,45);
        
    }
}
